Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports NENAData.NENAMember
Imports NENAData.DLC
Imports NENAData.LINQ
Imports NENAData.NENAProvinces

Public Class ApplicationForm
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub
    Protected WithEvents lblOtherCountry As System.Web.UI.WebControls.Label
    Protected WithEvents txtOtherCountry As System.Web.UI.WebControls.TextBox
    Protected WithEvents RequiredFieldValidator5 As System.Web.UI.WebControls.RequiredFieldValidator
    Protected WithEvents Image1 As System.Web.UI.WebControls.Image
    Protected WithEvents PersonalFirstName As System.Web.UI.WebControls.TextBox
    Protected WithEvents RequiredFieldValidator1 As System.Web.UI.WebControls.RequiredFieldValidator
    Protected WithEvents PersonalLastName As System.Web.UI.WebControls.TextBox
    Protected WithEvents RequiredFieldValidator2 As System.Web.UI.WebControls.RequiredFieldValidator
    Protected WithEvents PersonalAddress1 As System.Web.UI.WebControls.TextBox
    Protected WithEvents RequiredFieldValidator3 As System.Web.UI.WebControls.RequiredFieldValidator
    Protected WithEvents PersonalAddress2 As System.Web.UI.WebControls.TextBox
    Protected WithEvents RequiredFieldValidator4 As System.Web.UI.WebControls.RequiredFieldValidator
    Protected WithEvents lblOtherState As System.Web.UI.WebControls.Label
    Protected WithEvents txtOtherState As System.Web.UI.WebControls.TextBox
    Protected WithEvents PersonalEmail As eWorld.UI.MaskedTextBox
    Protected WithEvents PersonalHomePhone As eWorld.UI.MaskedTextBox
    Protected WithEvents txtWorkPhone As eWorld.UI.MaskedTextBox
    Protected WithEvents chkEmployeeAdministration As System.Web.UI.WebControls.CheckBox
    Protected WithEvents chkEmployeeClinical As System.Web.UI.WebControls.CheckBox
    Protected WithEvents chkEmployeeEducator As System.Web.UI.WebControls.CheckBox
    Protected WithEvents chkEmployeeResearch As System.Web.UI.WebControls.CheckBox
    Protected WithEvents chkENPCInstructor As System.Web.UI.WebControls.CheckBox
    Protected WithEvents chkENPCTrainer As System.Web.UI.WebControls.CheckBox
    Protected WithEvents chkENPCDirector As System.Web.UI.WebControls.CheckBox
    Protected WithEvents chkCATNProvider As System.Web.UI.WebControls.CheckBox
    Protected WithEvents chkCATNInstructor As System.Web.UI.WebControls.CheckBox
    Protected WithEvents chkCATNTrainer As System.Web.UI.WebControls.CheckBox
    Protected WithEvents chkCATNDirector As System.Web.UI.WebControls.CheckBox
    Protected WithEvents chkTNCCInstructor As System.Web.UI.WebControls.CheckBox
    Protected WithEvents chkTNCCTrainer As System.Web.UI.WebControls.CheckBox
    Protected WithEvents chkTNCCDirector As System.Web.UI.WebControls.CheckBox
    Protected WithEvents chkTNCCProvider As System.Web.UI.WebControls.CheckBox
    Protected WithEvents chkENPCProvider As System.Web.UI.WebControls.CheckBox
    Protected WithEvents PersonalCity As System.Web.UI.WebControls.TextBox
    Protected WithEvents PersonalPostalCode As System.Web.UI.WebControls.TextBox
    Protected WithEvents ScriptManager1 As System.Web.UI.ScriptManager
    Protected WithEvents ddlPersonalProvince As System.Web.UI.WebControls.DropDownList
    Protected WithEvents ValidationSummary1 As System.Web.UI.WebControls.ValidationSummary
    Protected WithEvents Requiredfieldvalidator7 As System.Web.UI.WebControls.RequiredFieldValidator
    Protected WithEvents ValidationSummary2 As System.Web.UI.WebControls.ValidationSummary
    Protected WithEvents BtnCancel As System.Web.UI.HtmlControls.HtmlInputButton
    Protected WithEvents btnSend As System.Web.UI.WebControls.Button
    Protected WithEvents BtnSave As System.Web.UI.WebControls.Button
    Protected WithEvents lblAffiliate As System.Web.UI.WebControls.Label
    Protected WithEvents ddlAffiliate As System.Web.UI.WebControls.DropDownList
    Protected WithEvents lblFee As System.Web.UI.WebControls.Label
    Protected WithEvents txtProvinceID As System.Web.UI.WebControls.TextBox
    Protected WithEvents txtAffiliateID As System.Web.UI.WebControls.TextBox
    Protected WithEvents txtInternational As System.Web.UI.WebControls.TextBox
    Protected WithEvents chkENCCCertified As System.Web.UI.WebControls.CheckBox
    Protected WithEvents RangeValidator1 As System.Web.UI.WebControls.RangeValidator
    Protected WithEvents Requiredfieldvalidator8 As System.Web.UI.WebControls.RequiredFieldValidator
    Protected WithEvents txtNursesLicenseID As System.Web.UI.WebControls.TextBox
    Protected WithEvents validateCATNID As System.Web.UI.WebControls.RequiredFieldValidator
    Protected WithEvents txtCATNID As System.Web.UI.WebControls.TextBox
    Protected WithEvents lblCATNID As System.Web.UI.WebControls.Label
    Protected WithEvents ValidateENPCID As System.Web.UI.WebControls.RequiredFieldValidator
    Protected WithEvents txtENPCID As System.Web.UI.WebControls.TextBox
    Protected WithEvents lblENPCID As System.Web.UI.WebControls.Label
    Protected WithEvents validateTNCCID As System.Web.UI.WebControls.RequiredFieldValidator
    Protected WithEvents txtTNCCID As System.Web.UI.WebControls.TextBox
    Protected WithEvents lblTNCCID As System.Web.UI.WebControls.Label
    Protected WithEvents chkACTASProvider As System.Web.UI.WebControls.CheckBox
    Protected WithEvents chkACTASInstructor As System.Web.UI.WebControls.CheckBox
    Protected WithEvents chkACTASTrainer As System.Web.UI.WebControls.CheckBox
    Protected WithEvents chkACTASDirector As System.Web.UI.WebControls.CheckBox
    Protected WithEvents chkPCTASProvider As System.Web.UI.WebControls.CheckBox
    Protected WithEvents chkPCTASInstructor As System.Web.UI.WebControls.CheckBox
    Protected WithEvents chkPCTASTrainer As System.Web.UI.WebControls.CheckBox
    Protected WithEvents chkPCTASDirector As System.Web.UI.WebControls.CheckBox
    Protected WithEvents lbl_ENAODistricts As System.Web.UI.WebControls.Label
    Protected WithEvents ddl_ENAODistricts As System.Web.UI.WebControls.DropDownList
    Protected WithEvents rv_ENAODistricts As System.Web.UI.WebControls.RangeValidator
    Protected WithEvents txtEmployer As System.Web.UI.WebControls.TextBox
    Protected WithEvents chkCCTASProvider As System.Web.UI.WebControls.CheckBox
    Protected WithEvents chkCCTASTrainer As System.Web.UI.WebControls.CheckBox
    Protected WithEvents check_RNAO As System.Web.UI.WebControls.CheckBox

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        BtnCancel.CausesValidation = False
        BtnSave.CausesValidation = True

        If Session("newEmail") = "" Then
            Response.Redirect("disclaimer.aspx")
        End If
        PersonalEmail.Text = Session("newEmail")

        If Not IsPostBack Then
            Load_Province_DDL()
            '    Load_ENAODistricts_DDL()
        End If

        'Load_Years_DDL()
        If ddlPersonalProvince.SelectedItem.Text.ToString = "Other" Then
            lblOtherState.Visible = True
            txtOtherState.Visible = True
            lblOtherCountry.Visible = True
            txtOtherCountry.Visible = True
        Else
            lblOtherState.Visible = False
            txtOtherState.Visible = False
            lblOtherCountry.Visible = False
            txtOtherCountry.Visible = False
        End If

    End Sub

    Public Sub SetAffiliate()
        If ddlPersonalProvince.SelectedValue > 0 Then
            ddlAffiliate.Visible = True
            lblAffiliate.Visible = True
            lblFee.Visible = True
            'load the affiliate ddl according to the selected province
            'only execute the affiliate ddl when the province changes
            If txtProvinceID.Text <> ddlPersonalProvince.SelectedValue.ToString Then
                Load_Affiliate_DDL()
            End If
            'update the label to display the proper fee
            SetFee()
        Else
            ddlAffiliate.Visible = False
            lblAffiliate.Visible = False
            lblFee.Visible = False
        End If
        txtProvinceID.Text = ddlPersonalProvince.SelectedValue.ToString

    End Sub

    Public Sub SetFee()
        Show_Fee()

        'If ddlPersonalProvince.SelectedValue.ToString = "5" Then
        '    lbl_ENAODistricts.Visible = True
        '    rv_ENAODistricts.Visible = True
        '    ddl_ENAODistricts.Visible = True
        '    check_RNAO.Visible = True
        'Else
        '    lbl_ENAODistricts.Visible = False
        '    rv_ENAODistricts.Visible = False
        '    ddl_ENAODistricts.Visible = False
        '    check_RNAO.Visible = False
        'End If
    End Sub

    Sub Show_Fee()
        Dim x As New NENAAffiliate
        Dim oAffiliate As Affiliate
        oAffiliate = x.GetAffiliate_byAffiliateID(CType(ddlAffiliate.SelectedItem.Value.ToString, Integer))

        lblFee.Text = "The fee for " & ddlAffiliate.SelectedItem.Text.ToString & _
                " is " & String.Format("{0:C}", oAffiliate.Membership_Fee)

        Session("payment_amt") = String.Format("{0:00.00}", oAffiliate.Membership_Fee)

        If oAffiliate.Affiliate_ID = 10 Then
            txtInternational.Visible = True
            txtInternational.Text = "International Members: Please also include your state and country in the City field above"
        Else
            txtInternational.Visible = False
        End If

    End Sub

    Private Sub Load_ENAODistricts_DDL()
        Dim x As ENAODistricts = New ENAODistricts

        ddl_ENAODistricts.DataSource = x.GetDistricts_All()
        ddl_ENAODistricts.DataValueField = "District_ID"
        ddl_ENAODistricts.DataTextField = "District_Description" '"Province_ID"
        ddl_ENAODistricts.DataBind()
        ddl_ENAODistricts.Items.Insert(0, "--Select--")
    End Sub

    Private Sub Load_Province_DDL()
        Dim x As NENAProvinces = New NENAProvinces

        ddlPersonalProvince.DataSource = x.GetProvinces_All()
        ddlPersonalProvince.DataValueField = "Province_ID"
        ddlPersonalProvince.DataTextField = "ProvinceName" '"Province_ID"
        ddlPersonalProvince.DataBind()
    End Sub

    Private Sub Load_Affiliate_DDL()
        'Dim x As NENAAffiliate = New NENAAffiliate

        ddlAffiliate.DataSource = Get_Affiliate_DDL()
        ddlAffiliate.DataValueField = "affiliate_id"
        'ddlAffiliate.DataValueField = "XREF_ID"
        ddlAffiliate.DataTextField = "affiliate_description"
        ddlAffiliate.DataBind()
    End Sub

    Public Sub BtnCancel_ServerClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnCancel.ServerClick
        Response.Redirect("default.aspx")
    End Sub

    Private Function EvaluateChecked(ByVal strChecked) As String
        If strChecked = "false" Then
            Return ("N")
        Else
            Return ("Y")
        End If
    End Function

    Private Function GetMemberID(ByVal strLastName As String, ByVal strFirstName As String, ByVal strAddress1 As String, ByVal strHomePhone As String) As Integer
        Dim conPubs As SqlConnection
        Dim cmdSelect As SqlCommand
        Dim dtrTitles As SqlDataReader
        Dim strSql As String

        strSql = "select * from members where " & _
       "lastname = '" & strLastName & "'" & _
       "and firstname = '" & strFirstName & "'" & _
       "and address = '" & strAddress1 & "'" & _
       "and homephone = '" & strHomePhone & "'"

        conPubs = Connections.GetSQLConnection
        cmdSelect = New SqlCommand(strSql, conPubs)
        conPubs.Open()
        dtrTitles = cmdSelect.ExecuteReader()

        While dtrTitles.Read
            GetMemberID = dtrTitles("member_id")
        End While
        dtrTitles.Close()
        conPubs.Close()
    End Function

    Function SqlReplaceText(ByVal strSql As String) As String
        Dim strFixedSQL As String

        strFixedSQL = Replace(strSql, "'", "")
        strFixedSQL = Replace(strFixedSQL, "[", "")
        strFixedSQL = Replace(strFixedSQL, "%", "")
        'strFixedSQL = Replace(strFixedSQL, "_", "[_]") 
        'some people may have underscores in their lastname
        strFixedSQL = Replace(strFixedSQL, "--", "")

        'return the fixed sql
        SqlReplaceText = strFixedSQL
    End Function

    Private Sub chkENPCInstructor_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkENPCInstructor.CheckedChanged

        'enabled ID checking
        If chkENPCInstructor.Checked = True Then
            lblENPCID.Visible = True
            txtENPCID.Visible = True
            ValidateENPCID.Enabled = True
            ValidateENPCID.Visible = True
        End If

        'disable ID checking
        If chkENPCInstructor.Checked = False Then
            lblENPCID.Visible = False
            txtENPCID.Visible = False
            ValidateENPCID.Enabled = False
            ValidateENPCID.Visible = False
        End If
    End Sub

    Private Sub chkTNCCInstructor_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkTNCCInstructor.CheckedChanged

        'enabled ID checking
        If chkTNCCInstructor.Checked = True Then
            lblTNCCID.Visible = True
            txtTNCCID.Visible = True
            validateTNCCID.Enabled = True
            validateTNCCID.Visible = True
        End If
        'disable ID checking
        If chkTNCCInstructor.Checked = False Then
            lblTNCCID.Visible = False
            txtTNCCID.Visible = False
            validateTNCCID.Enabled = False
            validateTNCCID.Visible = False
        End If
    End Sub

    Private Sub BtnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSave.Click
        If Not Page.IsValid Then
            Exit Sub
        End If
        'do not write twice; redirect to the application form
        If Page.IsCallback Or Page.IsPostBack Then
            Response.Redirect("ApplicationFormCompleted.aspx", False)
        End If

        Try

            'only execute if an affiliate is selected
            If ddlPersonalProvince.SelectedIndex = 0 Then
                Exit Sub
            End If

            Dim strFirstName, strLastName, strEmail, strHomePhone As String
            Dim strAddress1, strAddress2, strCity, strPostalCode As String
            Dim strAffiliateId As String
            Dim strProvince, strState, strCountry, strWorkPhone As String
            Dim strAdministration, strClinical, strEducator, strResearch As String
            Dim strPCTASProvider, strPCTASInstructor, strPCTASTrainer, strPCTASDirector As String
            Dim strACTASProvider, strACTASInstructor, strACTASTrainer, strACTASDirector As String
            Dim strCCTASProvider, strCCTASTrainer As String
            Dim strENPCProvider, strENPCInstructor, strENPCTrainer, strENPCDirector As String
            Dim strCATNProvider, strCATNInstructor, strCATNTrainer, strCATNDirector As String
            Dim strTNCCProvider, strTNCCInstructor, strTNCCTrainer, strTNCCDirector As String
            Dim strENPCID, strCATNID, strTNCCID, strLicenseID As String
            Dim strSQL, strENCCCertified, strEmployer, strRNAO As String
            Dim boolOther As Boolean

            If ddlPersonalProvince.SelectedItem.Text.ToString = "Other" Then
                boolOther = True
            Else
                boolOther = False
            End If

            strFirstName = SqlReplaceText(PersonalFirstName.Text)
            strLastName = SqlReplaceText(PersonalLastName.Text)
            strEmail = SqlReplaceText(PersonalEmail.Text)
            strHomePhone = SqlReplaceText(PersonalHomePhone.Text)
            strAddress1 = SqlReplaceText(PersonalAddress1.Text)
            strAddress2 = SqlReplaceText(PersonalAddress2.Text)
            strCity = SqlReplaceText(PersonalCity.Text)
            strPostalCode = SqlReplaceText(PersonalPostalCode.Text)
            strAffiliateId = ddlAffiliate.SelectedValue.ToString
            strProvince = ddlPersonalProvince.SelectedItem.Text.ToString
            strState = SqlReplaceText(txtOtherState.Text)
            strCountry = SqlReplaceText(txtOtherCountry.Text)
            strWorkPhone = SqlReplaceText(txtWorkPhone.Text)
            strAdministration = EvaluateChecked(chkEmployeeAdministration.Checked)
            strClinical = EvaluateChecked(chkEmployeeClinical.Checked)
            strEducator = EvaluateChecked(chkEmployeeEducator.Checked)
            strResearch = EvaluateChecked(chkEmployeeResearch.Checked)

            strENCCCertified = EvaluateChecked(chkENCCCertified.Checked)

            strPCTASProvider = EvaluateChecked(chkPCTASProvider.Checked)
            strPCTASInstructor = EvaluateChecked(chkPCTASInstructor.Checked)
            strPCTASTrainer = EvaluateChecked(chkPCTASTrainer.Checked)
            strPCTASDirector = EvaluateChecked(chkPCTASDirector.Checked)

            strACTASProvider = EvaluateChecked(chkACTASProvider.Checked)
            strACTASInstructor = EvaluateChecked(chkACTASInstructor.Checked)
            strACTASTrainer = EvaluateChecked(chkACTASTrainer.Checked)
            strACTASDirector = EvaluateChecked(chkACTASDirector.Checked)

            strCCTASProvider = EvaluateChecked(chkCCTASProvider.Checked)
            strCCTASTrainer = EvaluateChecked(chkCCTASTrainer.Checked)

            strENPCProvider = EvaluateChecked(chkENPCProvider.Checked)
            strENPCInstructor = EvaluateChecked(chkENPCInstructor.Checked)
            strENPCTrainer = EvaluateChecked(chkENPCTrainer.Checked)
            strENPCDirector = EvaluateChecked(chkENPCDirector.Checked)

            strCATNProvider = EvaluateChecked(chkCATNProvider.Checked)
            strCATNInstructor = EvaluateChecked(chkCATNInstructor.Checked)
            strCATNTrainer = EvaluateChecked(chkCATNTrainer.Checked)
            strCATNDirector = EvaluateChecked(chkCATNDirector.Checked)

            strTNCCProvider = EvaluateChecked(chkTNCCProvider.Checked)
            strTNCCInstructor = EvaluateChecked(chkTNCCInstructor.Checked)
            strTNCCTrainer = EvaluateChecked(chkTNCCTrainer.Checked)
            strTNCCDirector = EvaluateChecked(chkTNCCDirector.Checked)

            strRNAO = EvaluateChecked(check_RNAO.Checked)

            strENPCID = SqlReplaceText(txtENPCID.Text)
            strCATNID = SqlReplaceText(txtCATNID.Text)
            strTNCCID = SqlReplaceText(txtTNCCID.Text)
            strLicenseID = SqlReplaceText(txtNursesLicenseID.Text)
            strEmployer = SqlReplaceText(txtEmployer.Text)

            strSQL = "insert into members " & _
                "(lastname, firstname, address, address2, " & _
                "city, postalcode, affiliate_id, province, "

            If boolOther Then
                strSQL &= "country, "
            End If

            'Dim strENAODistrict As String = ""
            'If ddlPersonalProvince.SelectedValue.ToString = "5" Then
            '    strENAODistrict = ddl_ENAODistricts.SelectedItem.Value
            '    strSQL &= "ENAODistrict_ID, "
            'End If

            strSQL &= "homephone, workphone, emailaddress, " & _
                "aoe_administration, aoe_clinical, aoe_educator, aoe_research, ENCC_Certified, " & _
                "tncc_provider, tncc_instructor, tncc_course_trainer, tncc_course_director, " & _
                "enpc_provider, enpc_instructor, enpc_course_trainer, enpc_course_director, " & _
                "catn_provider, catn_instructor, catn_course_trainer, catn_course_director, " & _
                "pctas_provider, pctas_instructor, pctas_course_trainer, pctas_course_director, " & _
                "actas_provider, actas_instructor, actas_course_trainer, actas_course_director, "
            strSQL &= "cctas_provider, cctas_trainer, rnao, " & _
                "ENPC_Trainer_ID, TNCC_Trainer_ID, Nurses_License_ID,  " & _
                "employer, referring_code) "
            strSQL &= " values " & _
                "('" & strLastName & "', '" & strFirstName & "', '" & strAddress1 & "', '" & strAddress2 & "', '" & _
                strCity & "', '" & strPostalCode & "', '" & strAffiliateId & "', '"

            If boolOther Then
                strSQL &= strState & "', '" & strCountry & "', '"
            Else
                strSQL &= strProvince & "', '"
            End If

            'If ddlPersonalProvince.SelectedValue.ToString = "5" Then
            '    strSQL &= strENAODistrict & "', '"
            'End If
            Dim strReferral_Code As String = Session("ReferralId")

            strSQL &= strHomePhone & "', '" & strWorkPhone & "', '" & strEmail & "', '" & _
                strAdministration & "', '" & _
                strClinical & "', '" & strEducator & "', '" & strResearch & "', '" & strENCCCertified & "', '" & _
                strTNCCProvider & "', '" & strTNCCInstructor & "', '" & strTNCCTrainer & "', '" & strTNCCDirector & "', '" & _
                strENPCProvider & "', '" & strENPCInstructor & "', '" & strENPCTrainer & "', '" & strENPCDirector & "', '" & _
                strCATNProvider & "', '" & strCATNInstructor & "', '" & strCATNTrainer & "', '" & strCATNDirector & "', '" & _
                strPCTASProvider & "', '" & strPCTASInstructor & "', '" & strPCTASTrainer & "', '" & strPCTASDirector & "', '" & _
                strACTASProvider & "', '" & strACTASInstructor & "', '" & strACTASTrainer & "', '" & strACTASDirector & "', '" & _
                strCCTASProvider & "', '" & strCCTASTrainer & "', '" & strRNAO & "', '" & _
                strENPCID & "', '" & strTNCCID & "', '" & strLicenseID & "', '"
            strSQL &= strEmployer & "', '" & Session("ReferralId") & " '); "

            'Response.Write("strSQL: " & strSQL)

            Dim conPubs As SqlConnection
            Dim cmdSelect As SqlCommand

            conPubs = Connections.GetSQLConnection
            cmdSelect = New SqlCommand(strSQL, conPubs)
            conPubs.Open()
            cmdSelect.ExecuteNonQuery()
            conPubs.Close()

            Dim strUserSelectedAffiliate, strMemberID As String

            strUserSelectedAffiliate = ddlAffiliate.SelectedValue.ToString()
            strMemberID = GetMemberID(strLastName, strFirstName, strAddress1, strHomePhone)

            Session("Affiliate_ID") = strUserSelectedAffiliate
            Session("Member_ID") = strMemberID
            Session("Account_Status") = MemberStatus.Status.Pending

            Response.Redirect("ApplicationFormCompleted.aspx", False)
        Catch ex As Exception
            Throw ex

        End Try

    End Sub
    'todo: move to LINQ
    Function Get_Affiliate_DDL()
        Dim conPubs As SqlConnection
        Dim dtrTitles As SqlDataReader
        Dim cmdSelect As SqlCommand
        Dim strSql As String

        strSql = "select " & _
        "membership_fee, affiliate_full, affiliate_description, provinces.province, affiliate.affiliate_id " & _
        "from " & _
        "affiliate, affiliate_xref, provinces " & _
        "where  " & _
        "affiliate.affiliate_id = affiliate_xref.affiliate_id " & _
        "and affiliate_xref.province_id = provinces.province_id " & _
        "and affiliate_xref.province_id = " & ddlPersonalProvince.SelectedValue & ";"

        'conPubs = New SqlConnection(ConfigurationManager.AppSettings.Item("SQLConnectionString"))
        conPubs = Connections.GetSQLConnection
        cmdSelect = New SqlCommand(strSql, conPubs)
        conPubs.Open()
        dtrTitles = cmdSelect.ExecuteReader()

        Return dtrTitles
        dtrTitles.Close()
        conPubs.Close()
    End Function

End Class
